package com.ruoyi.common.utils;

import cn.hutool.core.util.StrUtil;
import com.ruoyi.common.exception.ServiceException;
import com.ruoyi.framework.config.ApplicationConfig;
import org.apache.commons.io.IOUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;

import java.io.*;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.text.DecimalFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.*;

/**
 * Excel导入导出工具
 * @auth yepanpan
 */
public class ExcelUtil {

    /**
     * 读取Excel中指定的工作表,返回字符串数据列表
     *
     * @param fields  字段映射，中文名=》字段
     * @param is    文件流
     * @param skip  跳过的行数
     * @param sheet 工作表序号
     * @return
     */
    public static List<Map<String,Object>> read(List<ExcelField> fields, InputStream is, int skip, int sheet) {
        List<Map<String,Object>> list = new ArrayList<>();
        try {
            Workbook wb = WorkbookFactory.create(is);
            Sheet ws = wb.getSheetAt(sheet);
            Row header = ws.getRow(skip-1);
            readField(fields, header);
            for (int i = skip; i < ws.getPhysicalNumberOfRows(); i++) {
                Row wr = ws.getRow(i);
                Map<String,Object> data = new HashMap<>();
                for(ExcelField field:fields){
                    if(wr.getPhysicalNumberOfCells() <= field.getIndex()){
                        continue;
                    }
                    Object v = readVal(wr.getCell(field.getIndex()));
                    if(v!= null && !"".equalsIgnoreCase(v.toString()) && !field.getOptions().isEmpty()){
                        v = field.getOptions().get(v.toString());
                    }
                    data.put(field.getCode(), v);
                }
                list.add(data);
            }
            is.close();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 解析列的对应关系
     * @param fields  字段映射，中文名=》字段
     * @param row
     * @return
     */
    public static void readField(List<ExcelField> fields, Row row){
        for(ExcelField field:fields){
            if(row.getPhysicalNumberOfCells() < field.getIndex()){

            }
        }
        for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
            Object v = readVal(row.getCell(i));
            if(v == null ||StrUtil.isEmpty(v.toString())){
                continue;
            }

            for(ExcelField field:fields){
                if(field.getLabel().equalsIgnoreCase(v.toString()) || ("*"+field.getLabel()).equalsIgnoreCase(v.toString())){
                    field.setIndex(i);
                }
            }
        }
    }


    /**
     * 生成Excel模板文件 2007以上格式
     *
     * @param fields  字段映射，中文名=》字段
     * @param os 输出流
     * @return XSSFWorkbook
     */
    public static void template(List<ExcelField> fields, OutputStream os) {
        XSSFWorkbook book = new XSSFWorkbook();
        XSSFSheet sheet = book.createSheet("数据模板");

        CellStyle style = sheet.getWorkbook().createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        Font titleFont = sheet.getWorkbook().createFont();
        titleFont.setFontName("Arial");
        titleFont.setFontHeightInPoints((short) 16);
        titleFont.setBold(true);
        style.setFont(titleFont);
        XSSFRow row = sheet.createRow(0);
        int i=0;
        for (ExcelField h:fields) {
            XSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            cell.setCellValue(h.getLabel());
            if(h.getOptions().isEmpty()){
                i++;
                continue;
            }
            String[] combo = h.getOptions().entrySet().stream().map(e->e.getKey()).toArray(String[]::new);
            if (combo.length > 15 || StrUtil.join(",", combo).length() > 255)
            {
                // 如果下拉数大于15或字符串长度大于255，则使用一个新sheet存储，避免生成的模板下拉值获取不到
                setXSSFValidationWithHidden(sheet, combo, "请选择", 1, 1000, i, i);
            }
            else
            {
                // 提示信息或只能选择不能输入的列内容.
                setPromptOrValidation(sheet, combo, "请选择", 1, 1000, i, i);
            }
            i++;
        }

        try {
            book.write(os);
            book.close();
            os.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 生成Excel文件 2007以上格式
     *
     * @param fields  字段映射，中文名=》字段
     * @param rows 数据
     * @return XSSFWorkbook
     */
    public static XSSFWorkbook exports(List<ExcelField> fields, List<Map<String,Object>> rows) {
        List<String> titles = new ArrayList<>();
        int i = 0;
        for(ExcelField e: fields){
            titles.add(e.getLabel());
        }

        XSSFWorkbook book = new XSSFWorkbook();
        XSSFSheet sheet = book.createSheet("数据");
        fillHeader(sheet, titles, 0);
        //空数据
        if(rows == null || rows.size() == 0){
            return book;
        }

        //填充表格数据
        for(i=0;i< rows.size();i++){
            Map data = rows.get(i);
            XSSFRow row = sheet.createRow(i+1);
            for(ExcelField e: fields){
                XSSFCell cell = row.createCell(e.getIndex());
                Object v = data.get(e.code);
                if(v == null){
                    continue;
                }else if(e.getOptions().isEmpty()){
                    writeVal(cell, v);
                }else if(!"".equalsIgnoreCase(v.toString())){
                    writeVal(cell, e.getOption(v.toString()));
                }
            }
        }
        return book;
    }

    /**
     * 导出到输出流
     *
     * @param fields  字段映射，中文名=》字段
     * @param rows 数据
     * @param os        OutputStream 输出流
     */
    public static void exports(List<ExcelField> fields, List<Map<String,Object>> rows, OutputStream os) {
        XSSFWorkbook book = exports(fields, rows);
        try {
            book.write(os);
            os.close();
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            IOUtils.closeQuietly(book);
        }
    }

    /**
     * 导出到文件
     *
     * @param fields  字段映射，中文名=》字段
     * @param rows 数据
     * @param filePath  String 输出路径
     */
    public static void exports(List<ExcelField> fields, List<Map<String,Object>> rows, String filePath) {
        try {
            exports(fields, rows, new FileOutputStream(filePath));
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 设置单元格的内容
     *
     * @param cell
     * @param v
     * @return
     */
    public static void writeVal(Cell cell, Object v) {
        XSSFSheet sheet = (XSSFSheet)cell.getSheet();
        if (v == null) {
            cell.setCellValue("");
        } else if (v instanceof String) {
            if (v != null && !"".equals(v) && v.toString().indexOf("\r\n") != -1) {
                XSSFCellStyle style = sheet.getWorkbook().createCellStyle();
                style.setWrapText(true);
                style.setAlignment(HorizontalAlignment.CENTER);
                style.setVerticalAlignment(VerticalAlignment.CENTER);
                cell.setCellStyle(style);
                cell.setCellValue(new XSSFRichTextString(v.toString()));
            } else {
                cell.setCellValue(v.toString());
            }
        } else if (v instanceof Integer) {
            cell.setCellValue(v.toString());
        } else if (v instanceof Double) {
            cell.setCellValue(v.toString());
        } else if (v instanceof Float) {
            cell.setCellValue(v.toString());
        } else if (v instanceof Long) {
            cell.setCellValue(v.toString());
        } else if (v instanceof Boolean) {
            cell.setCellValue((Boolean) v);
        } else if (v instanceof Date ) {
            DataFormat format = sheet.getWorkbook().createDataFormat();// 日期格式化
            CellStyle cellStyle = sheet.getWorkbook().createCellStyle();
            cellStyle.setDataFormat(format.getFormat("yyyy-MM-dd"));
            cell.setCellStyle(cellStyle);

            cell.setCellValue(ApplicationConfig.sdf.format((Date)v ));
        } else if (v instanceof LocalDateTime) {
            DataFormat format = sheet.getWorkbook().createDataFormat();// 日期格式化
            CellStyle cellStyle = sheet.getWorkbook().createCellStyle();
            cellStyle.setDataFormat(format.getFormat("yyyy-MM-dd HH:mm:ss"));
            cell.setCellStyle(cellStyle);

            cell.setCellValue(((LocalDateTime) v).format(ApplicationConfig.dtf));
        } else if (v instanceof Timestamp) {
            DataFormat format = sheet.getWorkbook().createDataFormat();// 日期格式化
            CellStyle cellStyle = sheet.getWorkbook().createCellStyle();
            cellStyle.setDataFormat(format.getFormat("yyyy-MM-dd HH:mm:ss"));
            cell.setCellStyle(cellStyle);

            cell.setCellValue(ApplicationConfig.sdf.format(new Date(((Timestamp) v).getTime())));
        } else if (v instanceof LocalDate) {
            DataFormat format = sheet.getWorkbook().createDataFormat();// 日期格式化
            CellStyle cellStyle = sheet.getWorkbook().createCellStyle();
            cellStyle.setDataFormat(format.getFormat("yyyy-MM-dd"));
            cell.setCellStyle(cellStyle);

            cell.setCellValue(((LocalDate) v).format(ApplicationConfig.df));
        } else if (v instanceof BigDecimal) {
            cell.setCellValue(((BigDecimal) v).doubleValue());
        } else {
            cell.setCellValue(v.toString());
        }
    }

    /**
     * 读取单元格的内容
     *
     * @param cell
     * @return
     */
    public static Object readVal(Cell cell) {
        if (cell == null){
            return "";
        }
        Object val = "";
        try
        {
            if (cell.getCellType() == CellType.NUMERIC || cell.getCellType() == CellType.FORMULA)
            {
                val = cell.getNumericCellValue();
                if (DateUtil.isCellDateFormatted(cell))
                {
                    val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换
                }
                else
                {
                    if ((Double) val % 1 != 0)
                    {
                        val = new BigDecimal(val.toString());
                    }
                    else
                    {
                        val = new DecimalFormat("0").format(val);
                    }
                }
            }
            else if (cell.getCellType() == CellType.STRING)
            {
                val = cell.getStringCellValue();
            }
            else if (cell.getCellType() == CellType.BOOLEAN)
            {
                val = cell.getBooleanCellValue();
            }
            else if (cell.getCellType() == CellType.ERROR)
            {
                val = cell.getErrorCellValue();
            }
        }
        catch (Exception e)
        {
            return val;
        }
        return val;
    }

    /**
     * 填充表头数据
     *
     * @param sheet  XSSFSheet 表格对象
     * @param titles List<String> 列标题
     * @param start   List<String[]> 数据
     */
    protected static void fillHeader(XSSFSheet sheet, List<String> titles, int start) {
        XSSFRow row = sheet.createRow(start);
        XSSFCellStyle style = sheet.getWorkbook().createCellStyle();
        style.setFillBackgroundColor(IndexedColors.GREEN.getIndex());
        style.setBorderBottom(BorderStyle.MEDIUM);
        style.setBorderTop(BorderStyle.MEDIUM);
        style.setBorderLeft(BorderStyle.MEDIUM);
        style.setBorderRight(BorderStyle.MEDIUM);
        XSSFFont font = sheet.getWorkbook().createFont();
        font.setBold(true);
        style.setFont(font);
        for (int i = 0; i < titles.size(); i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            cell.setCellValue(titles.get(i));
        }
    }

    /**
     * 填充第一行描述信息
     * @param sheet
     * @param title
     * @param cols
     */
    protected static void fillTitle(XSSFSheet sheet, String title, int cols) {
        XSSFRow row = sheet.createRow(0);
        XSSFCellStyle style = sheet.getWorkbook().createCellStyle();
        style.setFillBackgroundColor(IndexedColors.RED.getIndex());
        for (int i = 0; i < cols; i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            if (i == 0) {
                cell.setCellValue(title);
            }
        }
        if(cols > 1){
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, cols - 1));
        }
    }

    /**
     * 设置某些列的值只能输入预制的数据,显示下拉框（兼容超出一定数量的下拉框）.
     *
     * @param sheet 要设置的sheet.
     * @param textlist 下拉框显示的内容
     * @param promptContent 提示内容
     * @param firstRow 开始行
     * @param endRow 结束行
     * @param firstCol 开始列
     * @param endCol 结束列
     */
    public static void setXSSFValidationWithHidden(Sheet sheet, String[] textlist, String promptContent, int firstRow, int endRow, int firstCol, int endCol)
    {
        Workbook wb = sheet.getWorkbook();
        String hideSheetName = "combo_" + firstCol + "_" + endCol;
        Sheet hideSheet = wb.createSheet(hideSheetName); // 用于存储 下拉菜单数据
        for (int i = 0; i < textlist.length; i++)
        {
            hideSheet.createRow(i).createCell(0).setCellValue(textlist[i]);
        }
        // 创建名称，可被其他单元格引用
        Name name = wb.createName();
        name.setNameName(hideSheetName + "_data");
        name.setRefersToFormula(hideSheetName + "!$A$1:$A$" + textlist.length);
        DataValidationHelper helper = sheet.getDataValidationHelper();
        // 加载下拉列表内容
        DataValidationConstraint constraint = helper.createFormulaListConstraint(hideSheetName + "_data");
        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        // 数据有效性对象
        DataValidation dataValidation = helper.createValidation(constraint, regions);
        if (StrUtil.isNotEmpty(promptContent))
        {
            // 如果设置了提示信息则鼠标放上去提示
            dataValidation.createPromptBox("", promptContent);
            dataValidation.setShowPromptBox(true);
        }
        // 处理Excel兼容性问题
        if (dataValidation instanceof XSSFDataValidation)
        {
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        }
        else
        {
            dataValidation.setSuppressDropDownArrow(false);
        }

        sheet.addValidationData(dataValidation);
        // 设置hiddenSheet隐藏
        wb.setSheetHidden(wb.getSheetIndex(hideSheet), true);
    }

    /**
     * 设置 POI XSSFSheet 单元格提示或选择框
     *
     * @param sheet 表单
     * @param textlist 下拉框显示的内容
     * @param promptContent 提示内容
     * @param firstRow 开始行
     * @param endRow 结束行
     * @param firstCol 开始列
     * @param endCol 结束列
     */
    public static void setPromptOrValidation(Sheet sheet, String[] textlist, String promptContent, int firstRow, int endRow,
                                      int firstCol, int endCol)
    {
        DataValidationHelper helper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = textlist.length > 0 ? helper.createExplicitListConstraint(textlist) : helper.createCustomConstraint("DD1");
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        DataValidation dataValidation = helper.createValidation(constraint, regions);
        if (StrUtil.isNotEmpty(promptContent))
        {
            // 如果设置了提示信息则鼠标放上去提示
            dataValidation.createPromptBox("", promptContent);
            dataValidation.setShowPromptBox(true);
        }
        // 处理Excel兼容性问题
        if (dataValidation instanceof XSSFDataValidation)
        {
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        }
        else
        {
            dataValidation.setSuppressDropDownArrow(false);
        }
        sheet.addValidationData(dataValidation);
    }

    /**
     * Excel字段定义
     */
    public static class ExcelField{

        /** 解析字段代码 **/
        String code;
        /** 解析字段标签 **/
        String label;
        /** 字段验证号 **/
        Integer index;

        /** 下拉选项,导出是值->标签，导入是标签->值 **/
        Map<String, String> options = new HashMap<>();

        public String getCode() {
            return code;
        }

        public void setCode(String code) {
            this.code = code;
        }

        public String getLabel() {
            return label;
        }

        public void setLabel(String label) {
            this.label = label;
        }

        public Integer getIndex() {
            return index;
        }

        public void setIndex(Integer index) {
            this.index = index;
        }

        public Map<String, String> getOptions() {
            return options;
        }

        public void setOptions(Map<String, String> options) {
            this.options = options;
        }

        public void setOption(String value, String label) {
            this.options.put(value, label);
        }

        public String getOption(String value) {
            return this.options.get(value);
        }
    }
}
